package com.matt.foundation.sqlOperator;

import com.matt.foundation.utils.DateUtil;
import com.matt.foundation.utils.ModelUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/10/16
 * *******************************
 */
public class ChildrenOperator {

    public static final String remoteUrl = "jdbc:mysql://localhost:3306/vega";

    public static final String remoteName = "root";

    public static final String remotePWd = "123456";

//    public static final String remoteUrl = "jdbc:mysql://rm-bp1xxa9jd5ajt2km8o.mysql.rds.aliyuncs.com:3306/vega_dev";
//
//    public static final String remoteName = "matt";
//
//    public static final String remotePWd = "HgY5CcO4";


//    public static final String remoteUrl = "jdbc:mysql://rm-bp151a4j302p495nso.mysql.rds.aliyuncs.com:3306/vega_prd";
//
//    public static final String remoteName = "matt";
//
//    public static final String remotePWd = "HgY5CcO4";

    public static void main(String[] args) {
        int month = 5;
        while (month<=12){
            processCode(ModelUtil.numToStrLenth2(month));
            month++;
        }
    }


    public static void processCode(String month) {
        int num = 0;


        /**
         * 需要根据月份不断的查
         */
        try {
            Class.forName("com.mysql.jdbc.Driver");

            Connection remoteConn = DriverManager.getConnection(remoteUrl, remoteName, remotePWd);
            PreparedStatement remotePreparedStatement = null;
            ResultSet remoteResultSet = null ;
            String query = "select a.id, a.name, a.createTime, c.id, c.name, c.createTime,c.gender from children c LEFT  JOIN  area a ON c.aid = a.id  WHERE c.createTime like '%2017-"+ month + "%' ORDER BY a.createTime, c.createTime ";
            remotePreparedStatement = remoteConn.prepareStatement(query);

            remoteResultSet = remotePreparedStatement.executeQuery();
            Connection localConn = DriverManager.getConnection(remoteUrl, remoteName, remotePWd);
            PreparedStatement localPreparedStatement = null;
            Integer cdyjyChildren=0;
            Integer cdtzlChildren=0;
            Integer cdjjChildren=0;
            Integer shdlChildren=0;
            Integer shlgChildren=0;
            Integer bjtygChildren=0;
            ResultSet localResultSet = null;
            while (remoteResultSet.next()){
                String aId = remoteResultSet.getString(1); //aid
                String aName = remoteResultSet.getString(2); //aName
                Date aCreated = remoteResultSet.getDate(3); //aCreated
                String cId = remoteResultSet.getString(4); //cId
                String cName = remoteResultSet.getString(5); //cName
                Date cCreated = remoteResultSet.getDate(6); //cCreated
                Integer cGender = remoteResultSet.getInt(7); //cCreated

//                String aYear = DateUtil.millisToDateTime(aCreated.getTime(), "yyyy");
//                String aMonth = DateUtil.millisToDateTime(aCreated.getTime(), "MM");
                String cYear = DateUtil.millisToDateTime(cCreated.getTime(), "yyyy");
                String cMonth = DateUtil.millisToDateTime(cCreated.getTime(), "MM");
                System.out.println("aId :" + aId);
                System.out.println("aName :" +aName);
                System.out.println("cCreated :" +cCreated);
                System.out.println("cId :" + cId);
                System.out.println("cName :" +cName);

//                System.out.println("cCreated :" +cCreated);
                System.out.println("cYear :" +cYear);
                System.out.println("cMonth :" +cMonth);
                System.out.println("cGender :" +cGender);
                String cityCode = "";
                String areaOrder = "";

                String code = "";
                switch (aName ){
                    case "成都研究院" : cityCode = "51";areaOrder="002"; cdyjyChildren+=1;  code = cityCode + areaOrder + cYear + cMonth + ModelUtil.numToStr(cdyjyChildren) + cGender;break;
                    case "成都锦江麦宝乐园" : cityCode = "51";areaOrder="001"; cdjjChildren+=1; code = cityCode + areaOrder + cYear + cMonth + ModelUtil.numToStr(cdjjChildren) + cGender; break;
                    case "成都桐梓林麦宝乐园" : cityCode = "51";areaOrder="003"; cdtzlChildren+=1; code = cityCode + areaOrder + cYear + cMonth + ModelUtil.numToStr(cdtzlChildren) + cGender; break;
                    case "上海大宁麦宝乐园" : cityCode = "31";areaOrder="001"; shdlChildren+=1; code = cityCode + areaOrder + cYear + cMonth + ModelUtil.numToStr(shdlChildren) + cGender; break;
                    case "上海临港麦宝乐园" : cityCode = "31";areaOrder="002"; shlgChildren+=1; code = cityCode + areaOrder + cYear + cMonth + ModelUtil.numToStr(shlgChildren) + cGender; break;
                    case "北京太阳宫麦宝乐园" : cityCode = "11";areaOrder="001"; bjtygChildren+=1; code = cityCode + areaOrder + cYear + cMonth + ModelUtil.numToStr(bjtygChildren) + cGender;break;
                    default:break;
                }

                System.out.println(code);
                String update = "update children set code = ? where id=? ";
////
                localPreparedStatement = localConn.prepareStatement(update);
////
                localPreparedStatement.setString(1, code);
                localPreparedStatement.setString(2, cId);
                localPreparedStatement.execute();
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }


//    该城市的行政区划代码(两位数字)+该城市的园区顺序号(三位数字)+年份(4 位数字)+月份(两位数字)+入园学生顺序号(三位数字)+性别(1 位数字)。
//    北京11  上海31  成都51
//            园区及学生顺序按照创建时间排序
//    性别: 男1 女2
}
